# 存放需要经常用到的函数
import os
import shutil
import sqlite3
import zipfile
import pandas as pd
import numpy as np
from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
from cryptography.hazmat.primitives import padding
from cryptography.hazmat.backends import default_backend
from base64 import b64decode, b64encode
import datetime
from flask_jwt_extended import get_jwt
from functools import wraps
from flask import jsonify
import requests
import json
import time
from wechatpy import parse_message
from wechatpy.replies import TextReply, ImageReply
from wechatpy.utils import check_signature
from wechatpy.exceptions import InvalidSignatureException


# 前缀（全局变量）
DOMAIN = "http://api.cityinthesky.top/"


# 连接数据库
def get_db():
    db = sqlite3.connect('database/xcyg2.db')
    db.row_factory = sqlite3.Row  # 使得可以从查询结果中直接获取列名
    return db


def get_db_gzh():
    db = sqlite3.connect('database/gongzhonghao.db')
    db.row_factory = sqlite3.Row
    return db


def close_db(db):
    db.close()


# 从数据库中获取最新学生成绩记录id
def get_latest_id(condition):
    sqls = {
        "scores": ["SELECT MAX(id) FROM HistoryScoreD", "SELECT MAX(id) FROM CurrentScoreD"],
        "exams": ["SELECT MAX(exam_id) FROM HistoryExam", "SELECT MAX(exam_id) FROM Exam"],
        "scoreLines": ["SELECT MAX(id) FROM SchoolHistoryLineD", "SELECT MAX(id) FROM SchoolLineD"]
    }
    conn = None
    try:
        conn = get_db()
        cur = conn.cursor()
        cur.execute(sqls[condition][0])
        max_id_history = cur.fetchone()[0]
        cur.execute(sqls[condition][1])
        max_id_current = cur.fetchone()[0]
        max_id_history = 0 if max_id_history is None else max_id_history
        max_id_current = 0 if max_id_current is None else max_id_current
        return max(max_id_history, max_id_current) + 1
    except Exception as e:
        return None
    finally:
        if conn:
            close_db(conn)


# 跳跃式排名的自定义函数
def calculate_jump_rank(rank_series):
    rank_series = rank_series.dropna()  # 删除NaN值（总分为0的情况）
    unique_ranks = rank_series.unique()
    jump_ranks = {}
    next_rank = 1
    for rank in sorted(unique_ranks):
        count = len(rank_series[rank_series == rank])
        jump_ranks[rank] = next_rank
        next_rank += count
    return rank_series.map(jump_ranks)


# 判断成绩保留几位小数
def round_based_on_sum_check(row):
    sum_val = row['Sum']
    sum_check = row['Sum_check']
    sum_check_str = str(sum_check)
    if '.' in sum_check_str:
        decimal_part = sum_check_str.split('.')[1]
        decimal_length = len(decimal_part)
    else:
        decimal_length = 0
    rounded_sum = round(sum_val, decimal_length)
    return rounded_sum


# 判断上传的文件模板
def judgment_templates(df):
    df_columns = df.columns
    if len(df_columns) == 15:
        return "ty"
    elif len(df_columns) == 37:
        return "sty"
    else:
        return {"status": "error", "message": "上传失败，上传的文件格式不正确。"}


# 数据预处理
def df_preprocessing(df_new, df, judge_result):
    subject_list = {
        'ty': ['class', 'student_id', 'student_name', 'Sum_check', 'SumD_check', 'Yuwen',
               'Shuxue', 'Waiyu', 'Wuli', 'Huaxue', 'Shengwu', 'Zhengzhi', 'Lishi', 'Dili', 'Xiaoyuzhong'],
        'sty': ['class', 'student_id', 'student_name', 'Sum_check', 'SumD_check', 'SumRankCity', 'SumRankProvince',
                'Yuwen', 'YuwenRankCity', 'YuwenRankProvince', 'Shuxue', 'ShuxueRankCity', 'ShuxueRankProvince',
                'Waiyu', 'WaiyuRankCity', 'WaiyuRankProvince', 'Wuli', 'WuliRankCity', 'WuliRankProvince',
                'Huaxue', 'HuaxueRankCity', 'HuaxueRankProvince', 'Shengwu', 'ShengwuRankCity', 'ShengwuRankProvince',
                'Zhengzhi', 'ZhengzhiRankCity', 'ZhengzhiRankProvince', 'Lishi', 'LishiRankCity', 'LishiRankProvince',
                'Dili', 'DiliRankCity', 'DiliRankProvince', 'Xiaoyuzhong', 'XiaoyuzhongRankCity',
                'XiaoyuzhongRankProvince']
    }
    for cn in enumerate(subject_list[judge_result]):
        df_new[cn[1]] = df[cn[0]]
    df_new["WaiyuType"] = 1
    return df_new


# 检查总分
def check_sum(df_new):
    subject_list = ['Yuwen', 'Shuxue', 'Waiyu', 'Wuli', 'Huaxue', 'Shengwu', 'Zhengzhi', 'Lishi', 'Dili', 'Xiaoyuzhong']
    df_new['Sum'] = df_new[subject_list].sum(axis=1, skipna=True) + 0.001
    df_new['Sum'] = df_new.apply(round_based_on_sum_check, axis=1)
    df_new['Sum'] = np.where(df_new['Sum'] == 0, np.nan, df_new['Sum'])
    df_new['Mismatched_IDs'] = df_new.apply(
        lambda x: x['student_id'] if not pd.isna(x['Sum']) and not pd.isna(x['Sum_check']) and x['Sum'] != x[
            'Sum_check'] or pd.isna(x['Sum']) != pd.isna(x['Sum_check']) else None, axis=1)
    mismatched_ids = df_new[df_new['Mismatched_IDs'].notnull()]['Mismatched_IDs']
    if len(mismatched_ids) > 0:
        return {"status": "error", "message": "上传失败，学生的总分与校验值不匹配", "mismatched_ids":
            [str(int(student_id)) for student_id in mismatched_ids]}
    return df_new


# 检查总分排名
def check_sumd(df_new):
    df_new['SumD'] = df_new['Sum'].rank(method='min', ascending=False)
    df_new['SumD'] = calculate_jump_rank(df_new['SumD'])
    df_new['Mismatched_IDs'] = df_new.apply(
        lambda x: x['student_id'] if not pd.isna(x['SumD']) and not pd.isna(x['SumD_check']) and x['SumD'] != x[
            'SumD_check'] or pd.isna(x['SumD']) != pd.isna(x['SumD_check']) else None, axis=1)
    mismatched_ids = df_new[df_new['Mismatched_IDs'].notnull()]['Mismatched_IDs']
    if len(mismatched_ids) > 0:
        return {"status": "error", "message": "上传失败，学生的总分排名与校验值不匹配", "mismatched_ids":
            [str(int(student_id)) for student_id in mismatched_ids]}
    return df_new


# 计算排名
def calculate_ranking(df_new):
    subject_list = ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Wuli', 'Huaxue', 'Shengwu', 'Zhengzhi', 'Lishi', 'Dili',
                    'Xiaoyuzhong']
    for cn in subject_list[1:]:
        df_new[cn + 'D'] = df_new[cn].rank(method='min', ascending=False)
        df_new[cn + 'D'] = calculate_jump_rank(df_new[cn + 'D'])
        df_new.loc[df_new[cn] == 0, cn + 'D'] = 0
    for cn in subject_list:
        df_new[cn + 'B'] = df_new.groupby('class')[cn].rank(method='min', ascending=False)
        df_new[cn + 'B'] = df_new.groupby('class')[cn + 'B'].transform(calculate_jump_rank)
        df_new.loc[df_new[cn] == 0, cn + 'B'] = 0
    return df_new


# 修正外语成绩
def correct_waiyu(df_new, judge_result):
    condition = (df_new['Waiyu'].isna() | (df_new['Waiyu'] == 0)) & df_new['Xiaoyuzhong'].notna()
    df_new.loc[condition, 'WaiyuType'] = 2
    df_new.loc[condition, 'Waiyu'] = df_new.loc[condition, 'Xiaoyuzhong']
    df_new.loc[condition, 'WaiyuB'] = df_new.loc[condition, 'XiaoyuzhongB']
    df_new.loc[condition, 'WaiyuD'] = df_new.loc[condition, 'XiaoyuzhongD']
    if judge_result == 'sty':
        df_new.loc[condition, 'WaiyuRankCity'] = df_new.loc[condition, 'XiaoyuzhongRankCity']
        df_new.loc[condition, 'WaiyuRankProvince'] = df_new.loc[condition, 'XiaoyuzhongRankProvince']
    return df_new


# 插入成绩
def insert_scores(df_new, latest_id, params, judge_result):
    # 解析学生成绩新增学生信息并添加变动信息
    parse_student_info(df_new, params)
    subject_list = {
        'ty': ['id', 'student_id', 'school_id', 'year', 'student_grade', 'exam_id', 'class', 'Sum', 'SumB', 'SumD',
               'Yuwen', 'YuwenB', 'YuwenD', 'Shuxue', 'ShuxueB', 'ShuxueD', 'Waiyu', 'WaiyuB', 'WaiyuD', 'WaiyuType',
               'Wuli',
               'WuliB', 'WuliD', 'Huaxue', 'HuaxueB', 'HuaxueD', 'Shengwu', 'ShengwuB', 'ShengwuD', 'Zhengzhi',
               'ZhengzhiB', 'ZhengzhiD', 'Lishi', 'LishiB', 'LishiD', 'Dili', 'DiliB', 'DiliD', 'examtype', 'show'],
        'sty': ['id', 'student_id', 'school_id', 'year', 'student_grade', 'exam_id', 'class', 'Sum', 'SumB', 'SumD',
                'SumRankCity', 'SumRankProvince', 'Yuwen', 'YuwenB', 'YuwenD', 'YuwenRankCity', 'YuwenRankProvince',
                'Shuxue', 'ShuxueB', 'ShuxueD', 'ShuxueRankCity', 'ShuxueRankProvince', 'Waiyu', 'WaiyuB', 'WaiyuD',
                'WaiyuRankCity', 'WaiyuRankProvince', 'WaiyuType', 'Wuli', 'WuliB', 'WuliD', 'WuliRankCity',
                'WuliRankProvince',
                'Huaxue', 'HuaxueB', 'HuaxueD', 'HuaxueRankCity', 'HuaxueRankProvince', 'Shengwu', 'ShengwuB',
                'ShengwuD',
                'ShengwuRankCity', 'ShengwuRankProvince', 'Zhengzhi', 'ZhengzhiB', 'ZhengzhiD', 'ZhengzhiRankCity',
                'ZhengzhiRankProvince', 'Lishi', 'LishiB', 'LishiD', 'LishiRankCity', 'LishiRankProvince', 'Dili',
                'DiliB',
                'DiliD', 'DiliRankCity', 'DiliRankProvince', 'examtype', 'show']
    }
    sql = "INSERT INTO CurrentScoreD ({}) VALUES ({})".format(
        ", ".join(subject_list[judge_result]),
        ", ".join(["?"] * len(subject_list[judge_result]))
    )

    pd.set_option('future.no_silent_downcasting', True)
    df_new.fillna(0, inplace=True)

    conn = None
    try:
        conn = get_db()
        cur = conn.cursor()
        if judge_result == 'ty':
            for index, row in df_new.iterrows():
                cur.execute(sql, (
                    latest_id + index, row['student_id'], params['school_id'], params['year'], params['student_grade'],
                    params['exam_id'], row['class'], row['Sum'], row['SumB'], row['SumD'], row['Yuwen'], row['YuwenB'],
                    row['YuwenD'], row['Shuxue'], row['ShuxueB'], row['ShuxueD'], row['Waiyu'], row['WaiyuB'],
                    row['WaiyuD'], row['WaiyuType'], row['Wuli'], row['WuliB'], row['WuliD'], row['Huaxue'],
                    row['HuaxueB'],
                    row['HuaxueD'], row['Shengwu'], row['ShengwuB'], row['ShengwuD'], row['Zhengzhi'], row['ZhengzhiB'],
                    row['ZhengzhiD'], row['Lishi'], row['LishiB'], row['LishiD'], row['Dili'], row['DiliB'],
                    row['DiliD'], params['examtype'], params['show']))
        elif judge_result == 'sty':
            df_new.loc[(df_new['Sum_check'] == 0) & (df_new['SumD_check'] == 0), [
                'SumRankCity', 'SumRankProvince', 'YuwenRankCity', 'YuwenRankProvince', 'ShuxueRankCity',
                'ShuxueRankProvince', 'WaiyuRankCity', 'WaiyuRankProvince', 'WuliRankCity', 'WuliRankProvince',
                'HuaxueRankCity', 'HuaxueRankProvince', 'ShengwuRankCity', 'ShengwuRankProvince', 'ZhengzhiRankCity',
                'ZhengzhiRankProvince', 'LishiRankCity', 'LishiRankProvince', 'DiliRankCity', 'DiliRankProvince'
            ]] = 0
            for index, row in df_new.iterrows():
                cur.execute(sql, (
                    latest_id + index, row['student_id'], params['school_id'], params['year'], params['student_grade'],
                    params['exam_id'], row['class'], row['Sum'], row['SumB'], row['SumD'], row['SumRankCity'],
                    row['SumRankProvince'], row['Yuwen'], row['YuwenB'], row['YuwenD'], row['YuwenRankCity'],
                    row['YuwenRankProvince'], row['Shuxue'], row['ShuxueB'], row['ShuxueD'], row['ShuxueRankCity'],
                    row['ShuxueRankProvince'], row['Waiyu'], row['WaiyuB'], row['WaiyuD'], row['WaiyuRankCity'],
                    row['WaiyuRankProvince'], row['WaiyuType'], row['Wuli'], row['WuliB'], row['WuliD'],
                    row['WuliRankCity'],
                    row['WuliRankProvince'], row['Huaxue'], row['HuaxueB'], row['HuaxueD'], row['HuaxueRankCity'],
                    row['HuaxueRankProvince'], row['Shengwu'], row['ShengwuB'], row['ShengwuD'], row['ShengwuRankCity'],
                    row['ShengwuRankProvince'], row['Zhengzhi'], row['ZhengzhiB'], row['ZhengzhiD'],
                    row['ZhengzhiRankCity'], row['ZhengzhiRankProvince'], row['Lishi'], row['LishiB'], row['LishiD'],
                    row['LishiRankCity'], row['LishiRankProvince'], row['Dili'], row['DiliB'], row['DiliD'],
                    row['DiliRankCity'], row['DiliRankProvince'], params['examtype'], params['show']))
        conn.commit()
        return {"status": "success",
                "message": "{}模板上传成功，上传了{}条学生成绩记录。".format(judge_result, len(df_new))}
    except Exception as e:
        if conn:
            conn.rollback()
        return {"status": "error", "message": "服务器内部错误{}".format(e)}
    finally:
        if conn:
            conn.close()


# 解析学生成绩新增学生信息并添加变动信息
def parse_student_info(df, params):
    data = ["student_id", "student_name", "school_id", "year", "student_grade", "exam_id", "class"]
    df1 = df[data].copy()
    df1["school_id"] = params["school_id"]
    df1["year"] = params["year"]
    df1["student_grade"] = params["student_grade"]
    df1["exam_id"] = params["exam_id"]
    df1["status"] = "在读"
    conn = None
    try:
        conn = get_db()
        cur = conn.cursor()
        # 获取考试对应的年级
        sql = ("SELECT grade FROM Exam WHERE exam_id = ? "
               "UNION SELECT grade FROM HistoryExam WHERE exam_id = ?")
        exam_id = params["exam_id"]
        school_id = params["school_id"]
        cur.execute(sql, (exam_id, exam_id))
        grade = cur.fetchone()
        if grade:
            df1['grade'] = grade[0]
        else:
            df1['grade'] = None
        # 获取班级对应的选科
        df1['subject_selection'] = None
        for class_ in df1['class'].unique():
            sql1 = "SELECT subject_selection FROM Classes WHERE class = ? AND status = 'active'"
            cur.execute(sql1, (str(class_),))
            result = cur.fetchone()
            df1.loc[df1['class'] == class_, 'subject_selection'] = result[0] if result else None
        # 查询当前学期的学生信息
        sql2 = "SELECT * FROM CurrentStudent WHERE school_id = ?"
        cur.execute(sql2, (school_id,))
        current_students = cur.fetchall()
        current_students = [dict(row) for row in current_students]
        current_student_ids = [student['student_id'] for student in current_students]

        # 检查学生信息是否存在，不存在则插入
        for index, row in df1.iterrows():
            if row['student_id'] not in current_student_ids:
                if pd.isna(row['student_name']) or not row['student_name']:
                    continue
                sql3 = ("INSERT INTO CurrentStudent (student_id, student_name, school_id, year, student_grade, class, "
                        "subject_selection, grade, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
                cur.execute(sql3, (row['student_id'], row['student_name'], row['school_id'], row['year'],
                                   row['student_grade'], row['class'], row['subject_selection'], row['grade'],
                                   row['status']))
                # 插入学生变动信息
                change_type = '人员变动'
                change_method = '自动变更'
                change_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                remark = '解析学生成绩数据新增学生信息'
                previous_value = {}
                new_value = {}
                temp = "字段变动 "
                for key in row.keys():
                    if row[key] is not None:
                        previous_value[key] = None
                        new_value[key] = row[key]
                        temp += f"'{key}' : '' -> '{row[key]}'; "
                change_detail = f"""{change_type}, {temp}"""
                previous_value = str(previous_value)
                new_value = str(new_value)
                sql4 = (
                    "INSERT INTO StudentHistoryChange (school_id, student_id, change_type, previous_value, new_value, "
                    "change_detail, change_date, change_method, remark) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
                cur.execute(sql4, (school_id, row['student_id'], change_type, previous_value, new_value, change_detail,
                                   change_date, change_method, remark))
            # 更新学生信息
            else:
                # 比对学生信息是否有变动
                current_student = current_students[current_student_ids.index(row['student_id'])]
                fields_list = ['class', 'subject_selection', 'grade', 'year']
                for field in fields_list:
                    if not pd.isna(row[field]) and row[field] and str(row[field]) != current_student[field]:
                        # 更新学生信息
                        sql5 = f"UPDATE CurrentStudent SET {field} = ? WHERE student_id = ?"
                        cur.execute(sql5, (row[field], row['student_id']))
                        # 插入学生变动信息
                        change_type = ''
                        if field == 'class':
                            change_type = '班级变动'
                        elif field == 'subject_selection':
                            change_type = '选科变动'
                        elif field == 'grade':
                            change_type = '年级变动'
                        elif field == 'year':
                            change_type = '学年变动'
                        change_method = '自动变更'
                        change_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                        remark = '解析学生成绩数据更新学生信息'
                        previous_value = {field: current_student[field]}
                        new_value = {field: row[field]}
                        change_detail = f"""{change_type}, 字段变动 '{field}' : '{current_student[field]}' -> '{row[field]}'"""
                        previous_value = str(previous_value)
                        new_value = str(new_value)
                        sql6 = (
                            "INSERT INTO StudentHistoryChange (school_id, student_id, change_type, previous_value, "
                            "new_value, change_detail, change_date, change_method, remark) VALUES (?, ?, ?, ?, ?, ?, "
                            "?, ?, ?)")
                        cur.execute(sql6, (school_id, row['student_id'], change_type, previous_value, new_value,
                                           change_detail, change_date, change_method, remark))
        conn.commit()
    except Exception as e:
        pass
    finally:
        if conn:
            conn.close()


# 上传成绩
def upload_scores(df, params):
    latest_id = get_latest_id('scores')
    if latest_id is None:
        return {"status": "error", "message": "服务器内部错误，无法获取最新的学生成绩ID。"}
    judge_result = judgment_templates(df)
    if judge_result not in ['ty', 'sty']:
        return judge_result
    df.columns = range(len(df.columns))
    df.loc[df[3] == 0, [3, 4]] = np.nan
    df_new = pd.DataFrame(columns=[
        'id', 'student_id', 'student_name', 'school_id', 'year', 'student_grade', 'exam_id', 'class', 'Sum', 'SumB',
        'SumD', 'SumRankCity', 'SumRankProvince', 'Yuwen', 'YuwenB', 'YuwenD', 'YuwenRankCity', 'YuwenRankProvince',
        'Shuxue', 'ShuxueB', 'ShuxueD', 'ShuxueRankCity', 'ShuxueRankProvince', 'Waiyu', 'WaiyuB', 'WaiyuD',
        'WaiyuRankCity', 'WaiyuRankProvince', 'Wuli', 'WuliB', 'WuliD', 'WuliRankCity', 'WuliRankProvince', 'Huaxue',
        'HuaxueB', 'HuaxueD', 'HuaxueRankCity', 'HuaxueRankProvince', 'Shengwu', 'ShengwuB', 'ShengwuD',
        'ShengwuRankCity', 'ShengwuRankProvince', 'Zhengzhi', 'ZhengzhiB', 'ZhengzhiD', 'ZhengzhiRankCity',
        'ZhengzhiRankProvince', 'Lishi', 'LishiB', 'LishiD', 'LishiRankCity', 'LishiRankProvince', 'Dili', 'DiliB',
        'DiliD', 'DiliRankCity', 'DiliRankProvince', 'Xiaoyuzhong', 'XiaoyuzhongB', 'XiaoyuzhongD',
        'XiaoyuzhongRankCity', 'XiaoyuzhongRankProvince', 'Zonghe', 'ZongheB', 'ZongheD', 'ZongheRankCity',
        'ZongheRankProvince', 'Sum_check', 'SumD_check', 'WaiyuType'
    ])
    try:
        df_new = df_preprocessing(df_new, df, judge_result)
        df_new = check_sum(df_new)
        if isinstance(df_new, dict) and df_new['status'] == 'error':
            return df_new
        df_new = check_sumd(df_new)
        if isinstance(df_new, dict) and df_new['status'] == 'error':
            return df_new
        df_new = calculate_ranking(df_new)
        df_new = correct_waiyu(df_new, judge_result)
    except Exception as e:
        return {"status": "error", "message": "服务器内部错误{}".format(e)}
    return insert_scores(df_new, latest_id, params, judge_result)


# 从数据库中获取分数排名
def get_scoreline_d(df_new, params):
    subject_list = {
        'wl': ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Xiaoyuzhong', 'Wuli', 'Huaxue', 'Shengwu', 'Zhengzhi', 'Dili',
               'Zonghe'],
        'ls': ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Xiaoyuzhong', 'Huaxue', 'Shengwu', 'Zhengzhi', 'Lishi', 'Dili',
               'Zonghe']
    }
    conn = None
    try:
        conn = get_db()
        cur = conn.cursor()
        for index, row in df_new.iterrows():
            line_name = row['line_name']
            direction = 'wl' if line_name.startswith('物理方向') else 'ls'
            for sub in subject_list[direction]:
                original_sub = sub  # 保存原始科目名称
                if pd.isna(row[sub]):
                    df_new.loc[index, sub + 'D'] = np.nan
                    continue
                if sub == 'Xiaoyuzhong':
                    sub = 'Waiyu'
                condition_field = 'Wuli' if direction == 'wl' else 'Lishi'
                # 查询分数排名
                sql = f"""
                    SELECT {sub + 'D'} 
                    FROM CurrentScoreD 
                    WHERE exam_id = {params['exam_id']} AND {sub} >= {row[sub]} AND {condition_field} NOT NULL AND 
                    {condition_field} != 0
                    UNION
                    SELECT {sub + 'D'} 
                    FROM HistoryScoreD 
                    WHERE exam_id = {params['exam_id']} AND {sub} >= {row[sub]} AND {condition_field} NOT NULL AND 
                    {condition_field} != 0
                    ORDER BY {sub + 'D'} DESC
                    LIMIT 1
                """
                if original_sub == 'Waiyu':
                    sql = sql.replace("!= 0", "!= 0 AND (WaiyuType = 1 OR WaiyuType IS NULL)")
                elif original_sub == 'Xiaoyuzhong':
                    sql = sql.replace("!= 0", "!= 0 AND WaiyuType = 2")
                cur.execute(sql)
                result = cur.fetchone()
                df_new.loc[index, original_sub + 'D'] = result[0] if result else np.nan
        return df_new
    except Exception as e:
        return {"status": "error", "message": f"服务器内部错误：{e}"}
    finally:
        if conn:
            conn.close()


# 插入分数线
def insert_scoreline(df_new):
    # df_new.fillna(0, inplace=True)
    conn = None
    try:
        conn = get_db()
        cur = conn.cursor()
        for index, row in df_new.iterrows():
            if row['line_name'].startswith('物理方向'):
                sql = ("INSERT INTO SchoolLineD ('id','exam_id','line_name','Sum','SumD','Yuwen','YuwenD','Shuxue',"
                       "'ShuxueD','Waiyu','WaiyuD','Xiaoyuzhong','XiaoyuzhongD','Wuli','WuliD','Huaxue','HuaxueD',"
                       "'Shengwu','ShengwuD','Zhengzhi','ZhengzhiD','Dili','DiliD','Zonghe','ZongheD') VALUES (?,?,?,"
                       "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
                cur.execute(sql, (
                    row['id'], row['exam_id'], row['line_name'], row['Sum'], row['SumD'], row['Yuwen'], row['YuwenD'],
                    row['Shuxue'], row['ShuxueD'], row['Waiyu'], row['WaiyuD'], row['Xiaoyuzhong'], row['XiaoyuzhongD'],
                    row['Wuli'], row['WuliD'], row['Huaxue'], row['HuaxueD'], row['Shengwu'], row['ShengwuD'],
                    row['Zhengzhi'], row['ZhengzhiD'], row['Dili'], row['DiliD'], row['Zonghe'], row['ZongheD']
                ))
            elif row['line_name'].startswith('历史方向'):
                sql = ("INSERT INTO SchoolLineD ('id','exam_id','line_name','Sum','SumD','Yuwen','YuwenD','Shuxue',"
                       "'ShuxueD','Waiyu','WaiyuD','Xiaoyuzhong','XiaoyuzhongD','Huaxue','HuaxueD','Shengwu',"
                       "'ShengwuD','Zhengzhi','ZhengzhiD','Lishi','LishiD','Dili','DiliD','Zonghe','ZongheD') VALUES "
                       "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
                cur.execute(sql, (
                    row['id'], row['exam_id'], row['line_name'], row['Sum'], row['SumD'], row['Yuwen'], row['YuwenD'],
                    row['Shuxue'], row['ShuxueD'], row['Waiyu'], row['WaiyuD'], row['Xiaoyuzhong'], row['XiaoyuzhongD'],
                    row['Huaxue'], row['HuaxueD'], row['Shengwu'], row['ShengwuD'], row['Zhengzhi'], row['ZhengzhiD'],
                    row['Lishi'], row['LishiD'], row['Dili'], row['DiliD'], row['Zonghe'], row['ZongheD']
                ))
        conn.commit()
        return {"status": "success", "message": "分数线上传成功，上传了{}条分数线记录。".format(len(df_new))}
    except Exception as e:
        if conn:
            conn.rollback()
        return {"status": "error", "message": "服务器内部错误{}".format(e)}
    finally:
        if conn:
            conn.close()


# 上传分数线
def update_scoreline(df, params):
    latest_id = get_latest_id('scoreLines')
    if latest_id is None:
        return {"status": "error", "message": "服务器内部错误，无法获取最新的学生成绩ID。"}
    df.columns = range(len(df.columns))
    df_w = df.iloc[0:11, 0:6].rename(
        columns=dict(zip(range(6), ['物理方向', '二本线', '一本线', '211线', '985线', '清北线'])))
    df_l = df.iloc[0:11, 7:13].rename(
        columns=dict(zip(range(7, 13), ['历史方向', '二本线', '一本线', '211线', '985线', '清北线'])))
    df_w["物理方向"] = ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Xiaoyuzhong', 'Wuli', 'Huaxue', 'Shengwu', 'Zhengzhi',
                        'Dili', 'Zonghe']
    df_l["历史方向"] = ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Xiaoyuzhong', 'Huaxue', 'Shengwu', 'Zhengzhi', 'Lishi',
                        'Dili', 'Zonghe']
    df_w.set_index('物理方向', inplace=True)
    df_l.set_index('历史方向', inplace=True)
    rows = []
    # 合并物理方向的数据
    for cn in df_w.columns:
        if df_w[cn].isna().sum() < 5:
            row = {
                'id': latest_id + len(rows),
                'exam_id': params['exam_id'],
                'line_name': '物理方向' + cn,
            }
            row.update({sub: df_w.loc[sub, cn] for sub in df_w.index})
            rows.append(row)
    # 合并历史方向的数据
    for cn in df_l.columns:
        if df_l[cn].isna().sum() < 5:
            row = {
                'id': latest_id + len(rows),
                'exam_id': params['exam_id'],
                'line_name': '历史方向' + cn,
            }
            row.update({sub: df_l.loc[sub, cn] for sub in df_l.index})
            rows.append(row)
    df_new = pd.DataFrame(rows)
    df_new = get_scoreline_d(df_new, params)
    if isinstance(df_new, dict) and df_new['status'] == 'error':
        return df_new
    return insert_scoreline(df_new)


# AES解密
def decrypt_aes(encrypted_data):
    # 密钥和IV
    key = 'RfDqx3OzN4UZiu6AHi1sTE1d6lTrANvRfE61dkY9voQ='
    iv = 'm14iIljv577xjmYBj2GwIQ=='
    # 从Base64编码转换回字节
    key_bytes = b64decode(key)
    iv_bytes = b64decode(iv)

    # 创建解密器配置
    decryptor = Cipher(
        algorithms.AES(key_bytes),
        modes.CBC(iv_bytes),
        backend=default_backend()
    ).decryptor()

    # Base64解码密文并解密
    padded_plaintext = decryptor.update(b64decode(encrypted_data)) + decryptor.finalize()

    # 移除填充
    unpadder = padding.PKCS7(algorithms.AES.block_size).unpadder()
    plaintext = unpadder.update(padded_plaintext) + unpadder.finalize()

    return plaintext.decode('utf-8')


# 解压并重命名移动文件
def extract_and_rename_files(zip_file, extract_to_path, dest_path, subject_pinyin, prefix):
    # 解压文件
    with zipfile.ZipFile(zip_file, 'r') as zip_ref:
        zip_ref.extractall(extract_to_path)

    # 获取解压后的目录名
    extracted_dir = os.path.join(extract_to_path, os.listdir(extract_to_path)[0])
    files = os.listdir(extracted_dir)

    # 重命名并移动文件
    for file in files:
        file_path = os.path.join(extracted_dir, file)
        if os.path.isfile(file_path):
            os.makedirs(dest_path, exist_ok=True)
            if prefix != '':
                new_file_name = f'{file.split(".")[0]}-{subject_pinyin}-{prefix}.{file.split(".")[1]}'
            else:
                new_file_name = f'{file.split(".")[0]}-{subject_pinyin}.{file.split(".")[1]}'
            os.rename(file_path, os.path.join(dest_path, new_file_name))

    # 删除临时解压目录
    shutil.rmtree(extract_to_path)


# 检查当前用户是否有对应模块的指定操作权限
def check_permission(module, action):
    """检查当前用户是否有对应模块的指定操作权限"""
    claims = get_jwt()
    roles = claims.get('roles', [])
    conn = None
    try:
        conn = get_db()
        cur = conn.cursor()
        sql = "SELECT permissions FROM Roles WHERE role_name = ?"
        for role in roles:
            cur.execute(sql, (role,))
            result = cur.fetchone()
            if result:
                permissions_str = result[0]
                permissions = {}
                for permission in permissions_str.split('&'):
                    permissions[permission.split('-')[0]] = [i for i in permission.split('-')[1:]]
                if module in permissions and action in permissions[module]:
                    return True
        return False
    except Exception as e:
        return False
    finally:
        if conn:
            conn.close()


# 权限要求装饰器
def permission_required(module, action):
    """权限要求装饰器"""

    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            if not check_permission(module, action):
                return jsonify({
                    "code": 403,
                    "status": "error",
                    "message": "没有权限执行此操作"
                }), 403
            return f(*args, **kwargs)

        return decorated_function

    return decorator


# 客服消息小程序卡片
def send_mini_program_card(access_token, openid, app_id, page_path, thumb_media_id, title="小程序卡片"):
    url = f"https://api.weixin.qq.com/cgi-bin/message/custom/send?access_token={access_token}"
    data = {
        "touser": openid,
        "msgtype": "miniprogrampage",
        "miniprogrampage": {
            "title": title,
            "appid": app_id,
            "pagepath": page_path,
            "thumb_media_id": thumb_media_id
        }
    }
    response = requests.post(url, data=json.dumps(data, ensure_ascii=False).encode('utf-8'))
    print(response.json())
    return response.json()


# 客服消息文本
def send_text_message(access_token, openid, content):
    url = f"https://api.weixin.qq.com/cgi-bin/message/custom/send?access_token={access_token}"
    data = {
        "touser": openid,
        "msgtype": "text",
        "text": {
            "content": content
        }
    }
    response = requests.post(url, data=json.dumps(data, ensure_ascii=False).encode('utf-8'))
    return response.json()


# 客服消息图片
def send_image_message(access_token, openid, media_id):
    url = f"https://api.weixin.qq.com/cgi-bin/message/custom/send?access_token={access_token}"
    data = {
        "touser": openid,
        "msgtype": "image",
        "image": {
            "media_id": media_id
        }
    }
    response = requests.post(url, data=json.dumps(data, ensure_ascii=False).encode('utf-8'))
    return response.json()


# 公众号获取token和过期时间
def fetch_wechat_access_token(appid, appsecret):
    # 定义获取access token的URL
    token_url = f"https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={appid}&secret={appsecret}"

    # 发送请求获取access token
    response = requests.get(token_url)
    token_data = response.json()
    # 如果成功获取access token
    if 'access_token' in token_data:
        access_token = token_data['access_token']
        expires_in = token_data['expires_in']  # token有效期（秒）
        expire_time = time.time() + expires_in - 200  # 提前200秒更新token，防止过期
        return access_token, expire_time
    else:
        raise Exception(f"Failed to get access token: {token_data}")


# 处理构造回复消息
def handle_reply(response, msg, access_token):
    if response["reply_type"] == "text":
        reply = TextReply(content=response["reply_content"], message=msg)
        return reply.render()
    elif response["reply_type"] == "image":
        image_info = json.loads(response["reply_content"])
        reply = ImageReply(media_id=image_info["media_id"], message=msg)
        return reply.render()
    elif response["reply_type"] == "miniprogram":
        miniprogram_info = json.loads(response["reply_content"])
        send_mini_program_card(
            access_token,
            msg.source,
            miniprogram_info["app_id"],
            miniprogram_info["page_path"],
            miniprogram_info["thumb_media_id"],
            miniprogram_info["title"]
        )
        return "success"


# 处理回复类型为"all"的多条回复
def handle_all_replies(responses, msg, access_token):
    if responses:
        # 第一条消息通过自动回复
        first_response = responses.pop(0)
        auto_reply = handle_reply(first_response, msg, access_token)
        # 剩下的消息通过客服消息发送
        for response in responses:
            if response["reply_type"] == "text":
                send_text_message(access_token, msg.source, response["reply_content"])
            elif response["reply_type"] == "image":
                image_info = json.loads(response["reply_content"])
                send_image_message(access_token, msg.source, image_info["media_id"])
            elif response["reply_type"] == "miniprogram":
                miniprogram_info = json.loads(response["reply_content"])
                send_mini_program_card(
                    access_token,
                    msg.source,
                    miniprogram_info["app_id"],
                    miniprogram_info["page_path"],
                    miniprogram_info["thumb_media_id"],
                    miniprogram_info["title"]
                )
        return auto_reply


# 图片上传素材库获取media_id
def upload_image_to_wechat(access_token, image_file):
    url = f"https://api.weixin.qq.com/cgi-bin/material/add_material?access_token={access_token}&type=image"
    files = {'media': (image_file.filename, image_file, 'image/png')}
    try:
        response = requests.post(url, files=files)
        response.raise_for_status()  # 检查请求是否成功
        media_id = response.json().get('media_id')  # 尝试获取media_id
        url = response.json().get('url')  # 尝试获取url
        result = None
        if media_id and url:
            result = {
                "media_id": media_id
            }
        return result
    except requests.RequestException as e:
        print(f"请求错误: {e}")
        return None


# 删除公众号素材库图片
def delete_wechat_media(access_token, media_id):
    url = f"https://api.weixin.qq.com/cgi-bin/material/del_material?access_token={access_token}"
    data = {
        "media_id": media_id
    }
    try:
        response = requests.post(url, json=data)
        response.raise_for_status()  # 检查请求是否成功
        result = response.json()
        if result.get("errcode") == 0:
            return True
        else:
            return False
    except requests.RequestException as e:
        print(f"请求错误: {e}")
        return None


# 删除无用法的图片素材
def delete_media_files(cursor, rule_id, access_token):
    cursor.execute("SELECT reply_type, reply_content FROM replies WHERE rule_id = ?", (rule_id,))
    replies = cursor.fetchall()
    delete_files = []

    for reply in replies:
        if reply["reply_type"] == "image":
            reply_content = json.loads(reply["reply_content"])
            media_id = reply_content["media_id"]
            url = reply_content["url"]
            cursor.execute("SELECT COUNT(*) FROM replies WHERE reply_content LIKE ? AND rule_id != ?",
                           (f"%{media_id}%", rule_id))
            count = cursor.fetchone()[0]
            if count == 0:
                delete_files.append({"media_id": media_id, "url": url})
        elif reply["reply_type"] == "miniprogram":
            reply_content = json.loads(reply["reply_content"])
            thumb_media_id = reply_content["thumb_media_id"]
            thumb_url = reply_content["thumb_url"]
            cursor.execute("SELECT COUNT(*) FROM replies WHERE reply_content LIKE ? AND rule_id != ?",
                           (f"%{thumb_media_id}%", rule_id))
            count = cursor.fetchone()[0]
            if count == 0:
                delete_files.append({"media_id": thumb_media_id, "url": thumb_url})

    # 去重
    unique_delete_files = {f"{file['media_id']}_{file['url']}": file for file in delete_files}.values()

    for file in unique_delete_files:
        delete_wechat_media(access_token, file["media_id"])
        cursor.execute("DELETE FROM images WHERE url = ? AND media_id = ?", (file["url"], file["media_id"]))
        local_path = file["url"].replace("http://www.douknow.site/", "")
        try:
            os.remove(local_path)
        except FileNotFoundError:
            print(f"文件未找到: {local_path}")
        except Exception as e:
            print(f"删除文件时发生错误: {e}")
